﻿using System;
using System.Data;
using System.Data.OleDb;
using Remind.IDAL;
using Remind.Model;
using Remind.DBUtility;
using System.Collections.Generic;

namespace Remind.AccessDAL
{
    /// <summary>
    /// 分组信息access数据库访问实现类
    /// </summary>
    public class Group : IGroup
    {
        private const string SQL_SELECT_GROUPS = "select gid,gname from [group]";
        private const string SQL_SELECT_GROUP_BY_GNAME = "select gid,gname from [group] where gname=@gname";
        private const string SQL_SELECT_GROUP_BY_GID = "select gid,gname from [group] WHERE gid=@gid";
        private const string SQL_INSERT_GROUP = "insert into [group](gname)values(@gname)";
        private const string SQL_UPDATE_GROUP = "update [group] set gname=@gname where (gid=@gid)";
        private const string SQL_DELETE_GROUP = "delete from [group] where (gid=@gid)";

        private const string PARM_GNAME = "@gname";
        private const string PARM_GID = "@gid";

        #region IGroup 成员

        /// <summary>
        ///  查询所有分组信息      
        /// </summary>
        /// <returns></returns>
        public IList<GroupInfo> GetGroups()
        {
            IList<GroupInfo> groups = new List<GroupInfo>();

            GroupInfo group = null;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_GROUPS, null))
            {
                while (rdr.Read())
                {
                    group = new GroupInfo();
                    group.Gid = rdr.GetInt32(0);
                    group.Gname = rdr.GetString(1);
                    groups.Add(group);
                }
                return groups;
            }
        }

        /// <summary>
        /// 根据分组名查询分组信息
        /// </summary>
        /// <param name="gName"></param>
        /// <returns></returns>
        public GroupInfo GetGroupByGname(string gName)
        {
            OleDbParameter parm = new OleDbParameter(PARM_GNAME, OleDbType.VarChar, 16);
            parm.Value = gName;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_GROUP_BY_GNAME, parm))
            {
                GroupInfo group = null;
                if (rdr.Read())
                {
                    group = new GroupInfo();
                    group.Gid = rdr.GetInt32(0);
                    group.Gname = rdr.GetString(1);
                }
                return group;
            }
        }

        /// <summary>
        /// 编辑用户信息
        /// </summary>
        /// <param name="group"></param>
        /// <returns></returns>
        public int EditGroup(GroupInfo group, Idu idu)
        {
            int val = 0;
            
            switch (idu)
            {
                case Idu.insert:
                    OleDbParameter grouparmgname = new OleDbParameter(PARM_GNAME, OleDbType.VarChar, 16);
                    grouparmgname.Value = group.Gname;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_INSERT_GROUP, grouparmgname);       
                    break;
                case Idu.update:
                    OleDbParameter[] parms = new OleDbParameter[]{
            new OleDbParameter(PARM_GNAME,OleDbType.VarChar,16),
            new OleDbParameter(PARM_GID, OleDbType.Integer)
            };
                    parms[0].Value = group.Gname;
                    parms[1].Value = group.Gid;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_UPDATE_GROUP, parms);
                    break;
                case Idu.delete:
                    OleDbParameter parmgid = new OleDbParameter(PARM_GID, OleDbType.Integer);
                    parmgid.Value = group.Gid;
                    val = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, SQL_DELETE_GROUP, parmgid);
                    break;
            }
            return val;
        }

        public GroupInfo GetGroupInfoById(int gid)
        {
            OleDbParameter parm = new OleDbParameter(PARM_GID, OleDbType.Integer);
            parm.Value = gid;
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_GROUP_BY_GID, parm))
            {
                GroupInfo group = null;
                if (rdr.Read())
                {
                    group = new GroupInfo();
                    group.Gid = rdr.GetInt32(0);
                    group.Gname = rdr.GetString(1);
                }
                return group;
            }
        }
        #endregion
    }
}
